-- =============================================
-- Author:		Matthew Grdinic - nicSoft Fromboss
-- Create date: 10/2/08
-- Description:	Password Reminder
-- =============================================
CREATE PROCEDURE [dbo].[fb_admin_get_password_reminder] 
	-- Add the parameters for the stored procedure here
	@in_user_name VARCHAR(20), 
	@in_user_email VARCHAR(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT [user_name], user_email, pwd FROM fb_admin 
    WHERE [user_name] = @in_user_name AND user_email = @in_user_email;
END

-- =============================================
-- Author:		Matthew Grdinic - nicSoft Fromboss
-- Create date: 10/04/08
-- Description:	Get Unique Database Key Identifier
-- =============================================
CREATE PROCEDURE fb_get_db_key 
	-- Add the parameters for the stored procedure here
	@job_name VARCHAR(50),
	@created VARCHAR(12),
	@created_ts DATETIME,
	@last_edit VARCHAR(12),
	@user_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    INSERT INTO fb_jobs (job_name, created, created_ts, last_edit, [user_id]) 
    VALUES (@job_name, @created, @created_ts, @last_edit, @user_id); 
    
    SELECT SCOPE_IDENTITY() as id
END

-- =============================================
-- Author:		Matthew Grdinic - nicSoft Fromboss
-- Create date: 09/04/08
-- Description:	Get login authentication
-- =============================================
CREATE PROCEDURE [dbo].[fb_admin_get_authentication]
	-- Add the parameters for the stored procedure here
	@in_user_name VARCHAR(20), 
	@in_user_pass VARCHAR(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
        
    SELECT [user_name], [user_id], user_email, user_privilege, fb_auth.auth FROM fb_admin, fb_auth 
        WHERE [user_name] = @in_user_name AND user_pass = @in_user_pass;
END

-- =============================================
-- Author:		Matthew Grdinic - nicSoft Fromboss
-- Create date: 10/07/08
-- Description:	Adds user to db
-- =============================================
CREATE PROCEDURE [dbo].[fb_add_user] 
	@in_user_name VARCHAR(20),
	@in_user_email VARCHAR(50),
	@in_user_pass VARCHAR(50),
	@in_pwd VARCHAR(20),
	@in_user_privilege INT
	
AS

BEGIN

	DECLARE @message VARCHAR(255) = '';
	DECLARE @n_ct VARCHAR(255) = '';
	DECLARE @e_ct VARCHAR(255) = '';
	
	SELECT @n_ct = COUNT([user_name]) FROM fb_admin WHERE [user_name] = @in_user_name;
	SELECT @e_ct = COUNT(user_email) FROM fb_admin WHERE user_email = @in_user_email;
	
	IF @n_ct <> 0 AND @e_ct <> 0 BEGIN 
      SELECT @message = 'Notice: User Name and Email already exist.';
      END
		ELSE IF @n_ct <> 0 BEGIN 
      SELECT @message = 'Notice: User Name already exists.';
      END
		ELSE IF @e_ct <> 0 BEGIN
		SELECT @message = 'Notice: User Email already exists.'; 
	  END
	  
	  IF @message = '' BEGIN
	  	SELECT @message = 'User Added.'
		SELECT @message AS e_message;
		INSERT INTO fb_admin ([user_name], user_pass, pwd, user_email, user_privilege)
		VALUES (@in_user_name, @in_user_pass, @in_pwd, @in_user_email, @in_user_privilege);
		END
	ELSE BEGIN
		SELECT @message AS e_message;
	END;
	
END

-- =============================================
-- Author:		Matthew Grdinic - nicSoft Fromboss
-- Create date: 10/07/08>
-- Description:	Update user details
-- =============================================
CREATE PROCEDURE [dbo].[fb_update_login] 

	@in_user_id INT,
	@in_user_name VARCHAR(20),
	@in_user_email VARCHAR(50),
	@in_user_pass VARCHAR(50), 
	@in_pwd VARCHAR(20)
	
	AS

	DECLARE @message VARCHAR(255) = '';
	DECLARE @n_ct INT = 0;
	DECLARE @e_ct INT = 0;
	
	SELECT @n_ct = COUNT([user_name]) FROM fb_admin WHERE [user_name] = @in_user_name;
	SELECT @e_ct = COUNT(user_email) FROM fb_admin WHERE user_email = @in_user_email;
	
	BEGIN
	
	IF @in_user_pass = 'null'
		BEGIN
			IF @n_ct = 0
				BEGIN
					SET @message = 'User Name Updated'; 
					SELECT @message AS e_message;
					UPDATE fb_admin
					SET [user_name] = @in_user_name
					WHERE [user_id] = @in_user_id;
				END;
			IF @e_ct = 0
				BEGIN
					SET @message = 'User Email Updated'; 
					SELECT @message AS e_message;
					UPDATE fb_admin
					SET user_email = @in_user_email
					WHERE [user_id] = @in_user_id;
				END;

			IF @n_ct <> 0 AND @e_ct <> 0 BEGIN 
				SET @message = 'Notice: User Name and Email already exist and were not changed.'; 
				SELECT @message AS e_message;
				END
			ELSE IF @n_ct <> 0 BEGIN 
				SET @message = 'Notice: User Name already exists and was not changed.'; 
				SELECT @message AS e_message;
				END
			ELSE IF @e_ct <> 0 BEGIN 
				SET @message = 'Notice: User Email already exists and was not changed.'; 
				SELECT @message AS e_message;
				END
				 
		END
			
	ELSE
	BEGIN
		SELECT @message = 'Password Updated';
		SELECT @message AS e_message;
		UPDATE fb_admin
		SET user_pass = @in_user_pass,
			pwd = @in_pwd
		WHERE [user_id] = @in_user_id;
		
		-- now update login info
		
		IF @n_ct = 0
			BEGIN
				SET @message = 'User Name Updated'; 
				SELECT @message AS e_message;
				UPDATE fb_admin
				SET [user_name] = @in_user_name
				WHERE [user_id] = @in_user_id;
			END;
		IF @e_ct = 0
			BEGIN
				SET @message = 'User Email Updated'; 
				SELECT @message AS e_message;
				UPDATE fb_admin
				SET user_email = @in_user_email
				WHERE [user_id] = @in_user_id;
			END;

		IF @n_ct <> 0 AND @e_ct <> 0 BEGIN 
			SET @message = 'Notice: User Name and Email already exist and were not changed.'; 
			SELECT @message AS e_message;
			END
		ELSE IF @n_ct <> 0 BEGIN 
			SET @message = 'Notice: User Name already exists and was not changed.'; 
			SELECT @message AS e_message;
			END
		ELSE IF @e_ct <> 0 BEGIN 
			SET @message = 'Notice: User Email already exists and was not changed.'; 
			SELECT @message AS e_message;
			END
		
	END;

	END

-- =============================================
-- Author:		Matthew Grdinic - nicSoft Fromboss
-- Create date: 10/10/08
-- Description:	Get image from db
-- =============================================
CREATE PROCEDURE [dbo].[fb_get_image] 
	-- Add the parameters for the stored procedure here
	@in_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT file_data, file_mime, [file_name] FROM fb_job_entries WHERE id = @in_id;

END

-- =============================================
-- Author:		Matthew Grdinic - nicSoft Fromboss
-- Create date: 10/02/08
-- Description:	Get File From DB
-- =============================================
CREATE PROCEDURE [dbo].[fb_get_file] 
	-- Add the parameters for the stored procedure here
	@in_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT file_data, file_mime, [file_name] FROM fb_job_entries WHERE id = @in_id;

END

-- =============================================
-- Author:		Matthew Grdinic - nicSoft Fromboss
-- Create date: 4/19/09
-- Description:	Insert Image
-- =============================================
CREATE PROCEDURE [dbo].[fb_insert_image] 

	@in_entry_key VARCHAR(50),
	@in_job_id INT,
	@in_name VARCHAR(255),
	@in_value TEXT,
	@in_file_mime VARCHAR(50),
	@in_ts INT,
	@in_page_title VARCHAR(255),
	@in_jobname VARCHAR(50),
	@in_field_name VARCHAR(255),
	@in_label VARCHAR(255)
AS

	DECLARE @last_id INT;
	
	-- create job row if not exists
	DECLARE @ct INT;
	SELECT @ct = COUNT(id) FROM fb_jobs WHERE id = @in_job_id;
	
	DECLARE @qs VARCHAR(255);
	
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	IF @ct = 0 BEGIN
		INSERT INTO fb_jobs (id, job_name) VALUES (@in_job_id, @in_jobname);
	END
	
	BEGIN
		INSERT INTO fb_job_entries(job_id, entry_key) VALUES (@in_job_id, @in_entry_key);
		SELECT @last_id = SCOPE_IDENTITY();
	END
	
	-- create qs
	SELECT @qs = 'lib/get_image.php?id=' + CAST(@last_id AS VARCHAR(55));

	UPDATE fb_job_entries
		SET 
				ts = @in_ts,
				page_title = @in_page_title,
				name = @in_field_name, 
				label = @in_label, 
				entry_value = @qs,
				file_data = @in_value,
				file_mime = @in_file_mime,
				file_name = @in_name,
				entry_type = 'Image'
				WHERE id = @last_id;

END

-- =============================================
-- Author:		Matthew Grdinic - nicSoft Fromboss
-- Create date: 4/19/09
-- Description:	Insert File
-- =============================================
CREATE PROCEDURE [dbo].[fb_insert_file] 

	@in_entry_key VARCHAR(50),
	@in_job_id INT,
	@in_name VARCHAR(255),
	@in_value TEXT,
	@in_file_mime VARCHAR(50),
	@in_ts INT,
	@in_page_title VARCHAR(255),
	@in_jobname VARCHAR(50),
	@in_field_name VARCHAR(255),
	@in_label VARCHAR(255)
AS

	DECLARE @last_id INT;
	
	-- create job row if not exists
	DECLARE @ct INT;
	SELECT @ct = COUNT(id) FROM fb_jobs WHERE id = @in_job_id;
	
	DECLARE @qs VARCHAR(255);
	
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	IF @ct = 0 BEGIN
		INSERT INTO fb_jobs (id, job_name) VALUES (@in_job_id, @in_jobname);
	END
	
	BEGIN
		INSERT INTO fb_job_entries(job_id, entry_key) VALUES (@in_job_id, @in_entry_key);
		SELECT @last_id = SCOPE_IDENTITY();
	END
	
	-- create qs
	SELECT @qs = 'lib/get_file.php?id=' + CAST(@last_id AS VARCHAR(55));

	UPDATE fb_job_entries
		SET 
				ts = @in_ts,
				page_title = @in_page_title,
				name = @in_field_name, 
				label = @in_label, 
				entry_value = @qs,
				file_data = @in_value,
				file_mime = @in_file_mime,
				file_name = @in_name,
				entry_type = 'File'
				WHERE id = @last_id;

END

-- =============================================
-- Author:		Matthew Grdinic - nicSoft Fromboss
-- Create date: 10/02/08
-- Description:	Insert Entry Into DB
-- =============================================
CREATE PROCEDURE [dbo].[fb_insert_entry] 

	@in_entry_key VARCHAR(50),
	@in_job_id INT,
	@in_name VARCHAR(255),
	@in_label VARCHAR(255),
	@in_value TEXT,
	@in_ts INT,
	@in_page_title VARCHAR(255),
	@in_jobname VARCHAR(50),
	@in_remote_ip VARCHAR(50)
AS

	-- create job row if not exists
	DECLARE @ct INT;
	SELECT @ct = COUNT(id) FROM fb_jobs WHERE id = @in_job_id;
	
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	IF @ct = 0 BEGIN
		INSERT INTO fb_jobs (id, job_name) VALUES (@in_job_id, @in_jobname);
	END

	INSERT INTO fb_job_entries(entry_key, job_id, ts, page_title, name, label, entry_value, remote_ip)
		VALUES (
				@in_entry_key,
				@in_job_id, 
				@in_ts, 
				@in_page_title,
				@in_name,
				@in_label,
				@in_value,
				@in_remote_ip
		);
END